#!/bin/bash
#filename:cis_mysql.sh
#author:xlb
#date:2017-7-10
#version:1.0
#function:create database, create table,insert into data,select data.
#menu
menu(){
echo -e "\e[31m@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\e[0m"
echo "@database tools"
echo "@1.create database"
echo "@2.create table"
echo "@3.insert data"
echo "@4.query data"
echo "@5.exit"
echo -e "\e[31m@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\e[0m"

echo -n -e "\t\e[1;43;35mplease input your option (the first number per line):\e[0m";read option
}

#1.create databases
create_db(){
	read -p "please input database name:" u_dbname
	mysql -uroot -p123123 <<EOF
create database $u_dbname;
show databases;
quit
EOF

	echo -e "\e[32mcreate database $u_dbname ok\e[0m"
}
#2.create table
create_table(){
	mysql -uroot -p123123 <<EOF
show databases;
quit
EOF
	read -p "please input you need use database name:" u_db
	read -p "please input you need create table:" u_tab
	mysql -uroot -p123123 <<EOF
use ${u_db};
create table IF NOT EXISTS ${u_tab}(name varchar(20), 
id int(11) default 0);
show tables;
quit
EOF
	echo -e "\e[32mcreate table ${u_tab} success\e[0m"	
}
#3.insert data
insert_data(){
	mysql -uroot -p123123 <<EOF
show databases;
quit
EOF
	read -p "please input you need use database name:" u_db
	mysql -uroot -p123123 <<EOF
use $u_db;
show tables;
quit
EOF
	read -p "please input you need use table name:" u_tab
	mysql -uroot -p123123 <<EOF
use $u_db;
desc ${u_tab};
quit
EOF
	
	read -p "please input you need insert content of data:" name id 
	mysql -uroot -p123123 <<EOF
use $u_db;
insert into ${u_tab}(name,id) values("${name}","${id}");
quit
EOF
	echo -e "\e[32minsert data success\e[0m"	
}
#4.query data
query_data(){
	mysql -uroot -p123123 <<EOF
show databases;
quit
EOF
        read -p "please input you need use database name:" u_db
        mysql -uroot -p123123 <<EOF
use $u_db;
show tables;
quit
EOF
        read -p "please input you need selest table name:" u_tab
        mysql -uroot -p123123 <<EOF
use $u_db;
select * from ${u_tab};
quit
EOF
	
}

#main function 
main(){
	while :
	do 
	menu
	case $option in
	1)
		create_db
		;;
	2)
		create_table
		;;
	3)
		insert_data
		;;
	4)
		query_data
		;;
	5)
		exit
		;;
	*)
		exit
		;;
	esac
	done
}
main;
